#coding=utf-8

import sqlite3

class DataBase:
    sql_create_table_name=r'CREATE TABLE {}(id INTEGER PRIMARY KEY autoincrement);'
    sql_add_col=r'alter table {} add {} {} ;'
    sql_incert_a_data='''INSERT INTO {} ({}) VALUES ("{}");'''
    sql_incert_data='''INSERT INTO {table_name}{col_names}VALUES ({values})'''
    sql_search='''SELECT {} FROM {} WHERE {}="{}"'''
    sql_search_col='''SELECT {} from {}'''
    sql_update='''UPDATE {} SET {} = '{}' WHERE 诗人 ="{}"'''
    def translate_kws_list(self,col_name,col_type):
        if len(col_name)!=len(col_type):
            print("col_name 元素数量必须等于 col_type ")
        else:
            kws_list=[]
            for i in range(len(col_name)):
                kw=[]
                kw.append(col_name[i])
                kw.append(col_type[i])
                kws_list.append(kw)
            return kws_list
        
    def create_database(self,database_name):
        conn = sqlite3.connect(database_name)
        print("CREAT database {} successful".format(database_name))
        return conn

    def connect_database(self,database_name):
        conn = sqlite3.connect(database_name)
        return conn

    def add_a_col(self,table_name,cursor,kws):
        col_name=kws[0]
        col_type=kws[1]
        cursor.execute(self.sql_add_col.format(table_name,col_name,col_type))
        print("add col {} complete".format(col_name))

    def add_new_col(self,database_name,table_name,kws):
        connect=self.connect_database(database_name)
        my_cursor=connect.cursor()
        col_name=kws[0]
        col_type=kws[1]
        my_cursor.execute(self.sql_add_col.format(table_name,col_name,col_type))
        print("add new col {} complete".format(col_name))

    def incert_a_data(self,cursor,table,data):
        col_name=data[0]
        value=data[1]
        cursor.execute(self.sql_incert_a_data.format(table,col_name,value))
        print(">",end="")
        
    def create_table(self,database_name,table_name,col_name,col_type):
        '''
        CREATE a table for data base;
        database_name:string,target database name;
        table_name:string,name of the table to CREAT;
        *table_title:tuple,the title of the table
        '''
        connect=self.create_database(database_name)
        my_cursor=connect.cursor()
        my_cursor.execute(self.sql_create_table_name.format(table_name))
        kws_list=self.translate_kws_list(col_name,col_type)
        for kws in kws_list:
            self.add_a_col(table_name,my_cursor,kws)
        connect.commit()
        connect.close()
        print("create table {} complete".format(table_name))

    def incert_data_list_1(self,database_name,table_name,data_list):#这个方法会造成每次在数据库中填入一个数据就会行插入一行的bug，请是使用incert_data_list
        connect=self.connect_database(database_name)
        my_cursor=connect.cursor()
        my_cursor.execute("pragma table_info({})".format(table_name))
        for data in data_list:
            self.incert_a_data(my_cursor,table_name,data)
        connect.commit()
        connect.close()
        print("")
        print("incert data into table {} complete".format(table_name))

    def incert_data_list(self,database_name,table_name,data_list):
        col_name=self.get_table_infomation(database_name,table_name)
        col_name=str(col_name[1:])
        connect=self.connect_database(database_name)
        my_cursor=connect.cursor()
        value=str(data_list)[1:-1]#list 对象使用str()函数转换为 string 对象后，保留了"[]",因此使用[1:-1]切片，去除第一个和最后一个字符
        my_cursor.execute(self.sql_incert_data.format(table_name=table_name,col_names=col_name,values=value))
        connect.commit()
        connect.close()
        
    def get_table_infomation(self,database_name,table_name):
        "查询所有一个表中所有字段"
        connect=self.connect_database(database_name)
        my_cursor=connect.cursor()
        my_cursor.execute('pragma table_info({})'.format(table_name))
        col_name=my_cursor.fetchall()
        col_name=[x[1] for x in col_name]
        col_name=tuple(col_name)
        connect.close()
        return col_name

    def search(self,database_name,table_name,kw):
        connect=self.connect_database(database_name)
        my_cursor=connect.cursor()
        col_name_dasplay=kw[0]
        col_name_cal=kw[1]
        cal_value=kw[2]
        my_cursor.execute(self.sql_search.format(col_name_dasplay,table_name,col_name_cal,cal_value))
        result=[]
        for row in my_cursor:
            value=[]
            for i in range(len(row)):
                value.append(row[i])
            result.append(value)
        connect.close()
        return result

    def update(self,database_name,table_name,col_name,kw):
        connect=self.connect_database(database_name)
        my_cursor=connect.cursor()
        value=kw[0]
        author_name=kw[1]
        my_cursor.execute(self.sql_update.format(table_name,col_name,value,author_name))
        connect.commit()
        connect.close()
        
    def is_table_exit(self,database_name,table_name):
        connect=self.connect_database(database_name)
        my_cursor=connect.cursor()
        my_cursor.execute('''select count(*) from sqlite_master where type="table" and name="{}"'''.format(table_name))
        res=[]
        for item in  my_cursor:
            res.append(item)
        if res[0][0]==1:
            return True
        else:
            return False

    def search_col(self,database_name,table_name,col_name):
        connect=self.connect_database(database_name)
        my_cursor=connect.cursor()
        my_cursor.execute(self.sql_search_col.format(col_name,table_name))
        value_list=[]
        for value in my_cursor:
            value_list.append(value[0])
        return value_list

    def is_value_in_table(self,database_name,table_name,col_name,value):
        connect = self.connect_database(database_name)
        my_cursor = connect.cursor()
        my_cursor.execute("SELECT COUNT(*) FROM {} WHERE {}='{}'".format(table_name,col_name,value))
        res=[]
        for item in  my_cursor:
            res.append(item)
        if res[0][0]>0:
            return True
        else:
            return False